<% temporaryfolder=addbs(oprop.appstartpath)+[temp\] *temporaryfolder=[temp\] fromdate=orequest.form('datefrom') todate=orequest.form('dateto') if isnull(fromdate) fromdate=NVL(orequest.querystring('datefrom'),'{}') endif if isnull(todate) todate=NVL(orequest.querystring('dateto'),'{}') endif set date ymd pdate1=ctod(fromdate) pdate2=ctod(todate) set date british theglcode=orequest.form('gl') if isnull(theglcode) theglcode=orequest.querystring('gl') endif theglcode=val(transform(nvl(theglcode,''))) usesafe('acctran') usesafe('glcodes') locate for val(glcode)=theglcode theglname=allt(glcodes.name) usesafe('accounts') openingbalancealias=sys(2015) closingbalancealias=sys(2015) SELECT [acctran] SET ORDER TO acdate FILEstring=[

]+theglname+[ SUMMARY

]+CHR(13) tsql1=[total to (']+temporaryfolder+closingbalancealias+[') ON accountid fields debit, credit ] tsql2=[total to (']+temporaryfolder+openingbalancealias+[') ON accountid fields debit, credit ] for1text=[] for2text=[] do case case empty(pdate1) and empty(pdate2) FILEstring=FILESTRING+[

FROM START TO END

] case empty(pdate1) and not empty(pdate2) for1text=for1text+[ and date<=pdate2 ] FILEstring=FILESTRING+[

FROM START TO ]+DTOC(PDATE2)+[

] case not empty(pdate1) and empty(pdate2) for1text=for1text+[ and date>=pdate1 ] for2text=for2text+[ and dateFROM ]+DTOC(PDATE1)+[ TO END] case not empty(pdate1) and not empty(pdate2) for1text=for1text+[ and between(date,pdate1,pdate2) ] for2text=for2text+[ and dateFROM ]+DTOC(PDATE1)+[ TO ]+DTOC(PDATE2)+[] endcase if not empty(theglcode) for1text=for1text +[ and val(left(accountid,2))=theglcode ] for2text=for2text +[ and val(left(accountid,2))=theglcode ] endif if not empty(for1text) for1text=[ for ]+subst(for1text,5) endif if not empty(for2text) for2text=[ for ]+subst(for2text,5) endif tsql1=tsql1+for1text &tsql1 if not empty(pdate1) tsql2=tsql2+for2text &tsql2 else copy structure to (temporaryfolder+openingbalancealias) endif select * from (temporaryfolder+closingbalancealias) union all select * from (temporaryfolder+openingbalancealias) order by accountid, date ; into cursor temporaycursor use in select(openingbalancealias) use in select(closingbalancealias) ERASE ((temporaryfolder+closingbalancealias+[.dbf])) ERASE ((temporaryfolder+openingbalancealias+[.dbf])) SELECT [temporaycursor] GO top STORE 0 TO gldebits, glcredits filestring=FILESTRING+CHR(13)+[] store space(0) to debitstr, creditstr thisgl=[xX] DO WHILE NOT EOF() thisac=accountid =SEEK(thisac,[ACCOUNTS],[ACCOUNTID]) if left(thisac,2)<>thisgl thisgl=left(accountid,2) =seek(thisgl,[glcodes],[glcode]) theglname=iif(empty(allt(glcodes.name)),thisgl+[ has no glname],allt(glcodes.name)) glhead=[]+CHR(13) filestring=filestring+glhead+CHR(13) STORE 0 TO gldebits, glcredits endif THISACname=IIF(EMPTY(ALLTRIM(ACCOUNTS.name)),thisac+[ ACNAME MISSING],ALLT(ACCOUNTS.NAME))+[ ]+allt(accounts.phone) STORE 0 TO ACbalance glhead=[] DO WHILE accountid=thisAC AND NOT EOF() ACbalance=ACbalance+debit-credit skip ENDDO IF ACbalance>0 glhead=[] gldebits=gldebits+ACbalance debitstr=debitstr+glhead+chr(13) ENDIF IF ACbalance<0 glhead=[] glcredits=glcredits+abs(ACbalance) creditstr=creditstr+glhead+chr(13) ENDIF if empty(theglcode) and thisgl<>left(accountid,2) if gldebits>glcredits filestring=filestring+debitstr+CHR(13)+creditstr+chr(13) else filestring=filestring+creditstr+CHR(13)+debitstr+chr(13) endif debitstr=[] creditstr=[] IF gldebits>0 glhead=[] ELSE glhead=[] ENDIF filestring=filestring+glhead+CHR(13) IF glcredits>0 glhead=[] ELSE glhead=[] ENDIF filestring=filestring+glhead+CHR(13) if not empty(gldebits) and not empty(glcredits) if gldebits-glcredits>0 glhead=[] ELSE glhead=[] endif filestring=filestring+glhead+CHR(13) endif glhead=[] endif ENDDO if not empty(theglcode) if theglcode<6 filestring=filestring+debitstr+CHR(13)+creditstr+chr(13) else filestring=filestring+creditstr+CHR(13)+debitstr+chr(13) endif IF gldebits>0 glhead=[] ELSE glhead=[] ENDIF filestring=filestring+glhead+CHR(13) IF glcredits>0 glhead=[] ELSE glhead=[] ENDIF filestring=filestring+glhead+CHR(13) glhead=[] overallbalance=gldebits-glcredits if not empty(gldebits) and not empty(glcredits) if overallbalance>0 glhead=[] ELSE glhead=[] endif filestring=filestring+glhead+CHR(13) endif endif filestring=filestring+CHR(13)+[

DETAILS	DEBIT	CREDIT
]+theglname+[
]+THISACname+[	]+TRANSFORM(ABS(ACbalance),'999,999,999,999.99')+[
]+THISACname+[		]+TRANSFORM(abs(ACbalance),'999,999,999,999.99')+[
TOTALs:	]+TRANSFORM(gldebits,'999,999,999,999.99')+[
TOTALs:		]+TRANSFORM(abs(glcredits),'999,999,999,999.99')+[

]+theglname+[ BALANCE:	]+TRANSFORM(gldebits-glcredits,'999,999,999,999.99')+[
]+theglname+[ BALANCE:		]+TRANSFORM(glcredits-gldebits,'999,999,999,999.99')+[
TOTALs:	]+TRANSFORM(gldebits,'999,999,999,999.99')+[
TOTALs:		]+TRANSFORM(abs(glcredits),'999,999,999,999.99')+[

]+theglname+[ BALANCE:	]+TRANSFORM(overallbalance,'999,999,999,999.99')+[
]+theglname+[ BALANCE:		]+TRANSFORM(abs(overallbalance),'999,999,999,999.99')+[

] %>

<%=xcompany.name%>

<%=FILESTRING%>